--命名规则
--1.表名：lm开头
--2.字段取名：第一个字母大写
--3.字符长度：编号varchar(20)、普通长字符varchar(60)、备注varchar(250)、一个字符char、Int

--DROP TABLE Lm_Route
--select * FROM Lm_Route
CREATE TABLE Lm_Route(
	RouteId			varchar(10) NOT NULL,
	RouteName		varchar(20) NOT NULL,
	Description		varchar(200) NULL,
	CreateBy		varchar(20) NULL,
	CreateTime		datetime NULL,
	UpdateBy		varchar(20) NULL,
	UpdateTime		datetime NULL,
  	CONSTRAINT PK_Lm_Route PRIMARY KEY (RouteId)
);
exec sp_addextendedproperty 'MS_Description', '工艺流程', 'SCHEMA', 'dbo','TABLE', 'Lm_Route';
exec sp_addextendedproperty 'MS_Description', N'名称','SCHEMA', 'dbo', 'TABLE', 'Lm_Route', 'COLUMN', 'RouteName';
-- 工艺流程菜单
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by, create_time) 
VALUES ('工艺流程', 21136, 999, 'LmRoute', 'craft/LmRoute', 0, 0, 'C', '0', '0', 'lmroute:list', 'icon1', 'system', GETDATE());
-- 按钮父菜单id
declare @menuId int = @@identity
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('查询', @menuId, 1, '#', NULL, 0, 0, 'F', '0', '0', 'lmroute:query', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('新增', @menuId, 2, '#', NULL, 0, 0, 'F', '0', '0', 'lmroute:add', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('删除', @menuId, 3, '#', NULL, 0, 0, 'F', '0', '0', 'lmroute:delete', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('修改', @menuId, 4, '#', NULL, 0, 0, 'F', '0', '0', 'lmroute:edit', '', 'system', GETDATE());
SELECT * FROM sys_menu WHERE parentId = @menuId;
SELECT * FROM sys_menu WHERE menuId = @menuId;

--DROP TABLE Lm_RouteItem
--select * FROM Lm_RouteItem
CREATE TABLE Lm_RouteItem(
	RouteId			varchar(10) NOT NULL,
	ProcessId		varchar(10) NOT NULL,
	Description		varchar(200) NULL,
	Times			int DEFAULT 1 NULL,
	ProcessType		varchar(20) NOT NULL,
	Operate			varchar(20) NULL,
	TestNum			int DEFAULT 1 NULL,
	CheckType		varchar(20) NULL,
	SpotCheck		varchar(20) NULL,
	Material		varchar(20) NULL,
	DeviceID		varchar(10) NULL,
	CreateBy		varchar(20) NULL,
	CreateTime		datetime NULL,
	UpdateBy		varchar(20) NULL,
	UpdateTime		datetime NULL,
  	CONSTRAINT PK_Lm_RouteItem PRIMARY KEY (RouteId,ProcessId)
);
exec sp_addextendedproperty 'MS_Description', '工艺流程明细', 'SCHEMA', 'dbo','TABLE', 'Lm_RouteItem';
exec sp_addextendedproperty 'MS_Description', N'工序ID','SCHEMA', 'dbo', 'TABLE', 'Lm_RouteItem', 'COLUMN', 'ProcessId';

--A 2024-10-20 工序 ------Process route
--DROP TABLE Lm_Process
--select * FROM Lm_Process
CREATE TABLE Lm_Process(
	ProcessId		varchar(10) NOT NULL,
	ProcessName		varchar(20) NOT NULL,
	Times			int DEFAULT 1 NULL,
	ProcessType		varchar(20) NOT NULL,
	Operate			varchar(20) NULL,
	TestNum			int DEFAULT 1 NULL,
	CheckType		varchar(20) NULL,
	SpotCheck		varchar(20) NULL,
	Material		varchar(20) NULL,
	DeviceID		varchar(10) NULL,
	Create_by		varchar(20) NULL,
	Create_time		datetime NULL,
	Update_by		varchar(20) NULL,
	Update_time		datetime NULL,
	Description		varchar(200) NULL,
	Remark			varchar(200) NULL,
  	CONSTRAINT PK_Lm_Process PRIMARY KEY (ProcessId)
);
exec sp_addextendedproperty 'MS_Description', '工序', 'SCHEMA', 'dbo','TABLE', 'Lm_Process';
exec sp_addextendedproperty 'MS_Description', N'用时','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'Times';
exec sp_addextendedproperty 'MS_Description', N'类型','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'ProcessType';
exec sp_addextendedproperty 'MS_Description', N'操作类型','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'Operate';
exec sp_addextendedproperty 'MS_Description', N'测试数','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'TestNum';
exec sp_addextendedproperty 'MS_Description', N'品检类型','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'CheckType';
exec sp_addextendedproperty 'MS_Description', N'抽检类型','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'SpotCheck';
exec sp_addextendedproperty 'MS_Description', N'物料类型','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'Material';
exec sp_addextendedproperty 'MS_Description', N'设备编号','SCHEMA', 'dbo', 'TABLE', 'Lm_Process', 'COLUMN', 'DeviceID';
--添加字典数据
--ProcessType:lm_process_type:①过站信息note、②测试站点test、③品质站点quality、④绑定物料material、⑤绑定设备device
--note	test	quality		material	device
--Operate:lm_process_perate:①手动、②接口、③皆可
--workr		Interface		all
--TestNum:	0：不限次数；1：仅测1次；20以内：指定次数
--CheckType:lm_check_ype: ①抽检②全检
--spot	all
--SpotCheck:lm_spot_check: ①换班抽检②每天抽检③指定抽检
--change		day		appoint
--Material:lm_material_type: ①主料②辅料-不需要过站绑定，但面要记录用到③特殊物料-特殊处理
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by, create_time) 
VALUES ('工序', 21136, 999, 'LmProcess', 'craft/LmProcess', 0, 0, 'C', '0', '0', 'lmprocess:list', 'icon1', 'system', GETDATE());
-- 按钮父菜单id
declare @menuId int = @@identity
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('查询', @menuId, 1, '#', NULL, 0, 0, 'F', '0', '0', 'lmprocess:query', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('新增', @menuId, 2, '#', NULL, 0, 0, 'F', '0', '0', 'lmprocess:add', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('删除', @menuId, 3, '#', NULL, 0, 0, 'F', '0', '0', 'lmprocess:delete', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('修改', @menuId, 4, '#', NULL, 0, 0, 'F', '0', '0', 'lmprocess:edit', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('导出', @menuId, 5, '#', NULL, 0, 0, 'F', '0', '0', 'lmprocess:export', '', 'system', GETDATE());

SELECT * FROM sys_menu WHERE parentId = @menuId;
SELECT * FROM sys_menu WHERE menuId = @menuId;

--A 2024-9-23新增不良类型表------
--DROP TABLE Base_DefectType
--select DefectTypeId,DefectTypeName FROM Base_DefectType
CREATE TABLE Base_DefectType(
	DefectTypeId	varchar(10) NOT NULL,
	DefectTypeName	varchar(60) NOT NULL,
	Create_by		varchar(60) NULL,
	Create_time		datetime NULL,
	Update_by		varchar(60) NULL,
	Update_time		datetime NULL,
	Remark			varchar(200) NULL,
  CONSTRAINT [PK_Base_DefectType] PRIMARY KEY CLUSTERED 
  (
	[DefectTypeId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
exec sp_addextendedproperty 'MS_Description', '不良类型', 'SCHEMA', 'dbo','TABLE', 'Base_DefectType';
exec sp_addextendedproperty 'MS_Description', N'不良类型编号','SCHEMA', 'dbo', 'TABLE', 'Base_DefectType', 'COLUMN', 'DefectTypeId';
exec sp_addextendedproperty 'MS_Description', N'不良类型名称','SCHEMA', 'dbo', 'TABLE', 'Base_DefectType', 'COLUMN', 'DefectTypeName';
exec sp_addextendedproperty 'MS_Description', N'创建用户','SCHEMA', 'dbo', 'TABLE', 'Base_DefectType', 'COLUMN', 'Create_by'
exec sp_addextendedproperty 'MS_Description', N'创建时间','SCHEMA', 'dbo', 'TABLE', 'Base_DefectType', 'COLUMN', 'Create_time'
exec sp_addextendedproperty 'MS_Description', N'备注','SCHEMA', 'dbo', 'TABLE', 'Base_DefectType', 'COLUMN', 'Remark'
--select * FROM Base_DefectType ;
-- DefectType菜单
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by, create_time) 
VALUES ('DefectType', 1123, 999, 'BaseDefecttype', 'base/BaseDefecttype', 0, 0, 'C', '0', '0', 'defecttype:list', 'icon1', 'system', GETDATE());
-- 按钮父菜单id
declare @menuId int = @@identity

INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('查询', @menuId, 1, '#', NULL, 0, 0, 'F', '0', '0', 'defecttype:query', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('新增', @menuId, 2, '#', NULL, 0, 0, 'F', '0', '0', 'defecttype:add', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('删除', @menuId, 3, '#', NULL, 0, 0, 'F', '0', '0', 'defecttype:delete', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('修改', @menuId, 4, '#', NULL, 0, 0, 'F', '0', '0', 'defecttype:edit', '', 'system', GETDATE());
SELECT * FROM sys_menu WHERE parentId = @menuId;
SELECT * FROM sys_menu WHERE menuId = @menuId;
--A 2024-9-2新增不良原因表------
--DROP TABLE Base_DefectItem
CREATE TABLE Base_DefectItem (
	DefectItemId 	varchar(10) NOT NULL,
	DefectItemName 	varchar(60) NOT NULL,
	DefectTypeId	varchar(10) NOT NULL,
	Create_by		varchar(60) NULL,
	Create_time		datetime NULL,
	Update_by		varchar(60) NULL,
	Update_time		datetime NULL,
	Remark			varchar(200) NULL,
 CONSTRAINT [PK_Base_DefectItem] PRIMARY KEY CLUSTERED 
(
	DefectItemId ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
exec sp_addextendedproperty 'MS_Description', '不良原因', 'SCHEMA', 'dbo','TABLE', 'Base_DefectItem';
exec sp_addextendedproperty 'MS_Description', N'不良原因编号','SCHEMA', 'dbo', 'TABLE', 'Base_DefectItem', 'COLUMN', 'DefectItemId';
exec sp_addextendedproperty 'MS_Description', N'不良原因名称','SCHEMA', 'dbo', 'TABLE', 'Base_DefectItem', 'COLUMN', 'DefectItemName';
exec sp_addextendedproperty 'MS_Description', N'不良类型编号','SCHEMA', 'dbo', 'TABLE', 'Base_DefectItem', 'COLUMN', 'DefectTypeId';
--SELECT * FROM Base_DefectItem ;

-- 不良原因菜单
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by, create_time) 
VALUES ('不良原因', 1123, 999, 'baseDefectitem', 'base/baseDefectitem', 0, 0, 'C', '0', '0', 'basedefectitem:list', 'icon1', 'system', GETDATE());
-- 按钮父菜单id
declare @menuId int = @@identity
SELECT @menuId
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('查询', @menuId, 1, '#', NULL, 0, 0, 'F', '0', '0', 'basedefectitem:query', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('新增', @menuId, 2, '#', NULL, 0, 0, 'F', '0', '0', 'basedefectitem:add', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('删除', @menuId, 3, '#', NULL, 0, 0, 'F', '0', '0', 'basedefectitem:delete', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('修改', @menuId, 4, '#', NULL, 0, 0, 'F', '0', '0', 'basedefectitem:edit', '', 'system', GETDATE());
INSERT INTO sys_menu(menuName, parentId, orderNum, path, component, isFrame, isCache, menuType, visible, status, perms, icon, create_by,create_time) 
VALUES ('导出', @menuId, 5, '#', NULL, 0, 0, 'F', '0', '0', 'basedefectitem:export', '', 'system', GETDATE());
SELECT * FROM sys_menu WHERE parentId = @menuId;
SELECT * FROM sys_menu WHERE menuId = @menuId;
--SELECT * FROM sys_menu WHERE menuId = 21129;
--DELETE FROM sys_menu WHERE menuId in(21123,21124,21125,21126,21127,21128)
